import sqlite3


class dbs:

    def __init__(self):
        self.conn = sqlite3.connect('BUPT_Chat.db')

    def get_cursor(self):
        return self.conn.cursor()

    def init_database(self):
        c = self.get_cursor()
        # c.execute("DROP TABLE if exists users")
        # c.execute("DROP TABLE if exists room")
        # c.execute("DROP TABLE if exists member")
        # c.execute("DROP TABLE if exists history")
        # self.conn.commit()

        c.execute('''CREATE TABLE if not exists users (
                        "user_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                        "username" TEXT,
                        "password" TEXT,
                        "email" TEXT NOT NULL,
                        "code" TEXT,
                        "public_key" TEXT,
                        "public_key_n" TEXT
        )               ;''')
        self.conn.commit()
        c.execute('''CREATE TABLE if not exists room (
                            "room_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                            "room_name" TEXT,
                            "room_public_key" TEXT,
                            "room_public_key_n" TEXT,
                            "room_private_key" TEXT
            )               ;''')
        self.conn.commit()
        c.execute('''CREATE TABLE if not exists member (
                                 "room_id" INTEGER NOT NULL,
                                 "user_id" INTEGER NOT NULL,
                                  FOREIGN KEY ("room_id") REFERENCES "room" ("room_id") ON DELETE SET NULL ON UPDATE CASCADE,
                                  FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE SET NULL ON UPDATE CASCADE
                )               ;''')
        self.conn.commit()
        c.execute('''CREATE TABLE if not exists history (
                                "message_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                "user_id" INTEGER NOT NULL,
                                "room_id" INTEGER NOT NULL,
                                "time" text NOT NULL,
                                "data" blob,
                                "OTP_flag" integer DEFAULT 0,
                                FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE SET NULL ON UPDATE CASCADE,
                                 FOREIGN KEY ("room_id") REFERENCES "room" ("room_id") ON DELETE SET NULL ON UPDATE CASCADE
                )               ;''')
        self.conn.commit()

    def insert_email_information(self, email):
        c = self.get_cursor()
        c.execute("INSERT into users (email) values (?)", (email,))
        self.conn.commit()

    def insert_user_information(self, username, password, email, pubkey, pubkey_n):
        c = self.get_cursor()
        c.execute("UPDATE users SET username=?,password=?,public_key=?,public_key_n=? WHERE email=?",
                  (username, password, pubkey, pubkey_n, email))
        self.conn.commit()

    def insert_OTPflag(self, msg_id, OTP_flag):
        c = self.get_cursor()
        c.execute("UPDATE history SET OTP_flag = ? WHERE message_id=?",
                  (OTP_flag,msg_id))
        self.conn.commit()

    def insert_user_code(self, code, email):
        c = self.get_cursor()
        c.execute("UPDATE users SET code=? WHERE email=?", (code, email))
        self.conn.commit()

    def select_username_repeat(self, username):
        c = self.get_cursor()
        flag = c.execute("SELECT 1 from users where username=?", (username,)).fetchone()
        self.conn.commit()
        if flag == None:
            return 0  # 无重名
        else:
            return 1  # 有重名

    def select_email_repeat(self, email):
        c = self.get_cursor()
        flag = c.execute("SELECT 1 from users where email=? and username is not NULL", (email,)).fetchone()
        self.conn.commit()
        if flag == None:
            return 0  # 无重名
        else:
            return 1  # 有重名

    def select_password_right(self, username, password):
        c = self.get_cursor()
        flag = c.execute("SELECT 1 from users where username=? and password=?", (username, password)).fetchone()
        self.conn.commit()
        if flag == None:
            return 0
        else:
            return 1

    def select_code_right(self, email, code):
        c = self.get_cursor()
        flag = c.execute("SELECT 1 from users where email=? and code=?", (email, code)).fetchone()
        self.conn.commit()
        if flag == None:
            return 0
        else:
            return 1

    def query_userID_byname(self, username):
        c = self.get_cursor()
        user_id = c.execute("SELECT user_id from users where username=?", (username,)).fetchone()[0]
        return user_id

    def query_userID_byemail(self, email):
        c = self.get_cursor()
        user_id = c.execute("SELECT user_id from users where email=?", (email,)).fetchone()[0]
        return user_id

    def query_username_byuserID(self, user_id):
        c = self.get_cursor()
        username = c.execute("SELECT username from users where user_id=?", (user_id,)).fetchone()[0]
        return username

    def query_username_byemail(self, email):
        c = self.get_cursor()
        username = c.execute("SELECT username from users where email=?", (email,)).fetchone()[0]
        return username

    def clear_cache(self):
        c = self.get_cursor()
        c.execute(
            '''DELETE FROM users where user_id IN (SELECT user_id from users where username is NULL and email is not NULL);''')
        self.conn.commit()

    def query_public_key_public_key_n(self, user_id):
        c = self.get_cursor()
        pbks = c.execute("SELECT public_key,public_key_n FROM users where user_id=?", [user_id]).fetchall()[0]
        print(pbks)
        return [pbks[0], pbks[1]]

    def insert_member(self, room_id, user_id):
        c = self.get_cursor()
        c.execute("INSERT into member (room_id,user_id) values (?,?)", [room_id, user_id])
        self.conn.commit()

    def insert_room_information(self, room_name, room_public_key, room_public_key_n, room_private_key):
        c = self.get_cursor()
        c.execute("INSERT into room (room_name,room_public_key,room_public_key_n,room_private_key) values (?,?,?,?)",
                  [room_name, room_public_key, room_public_key_n, room_private_key])
        self.conn.commit()

    def insert_room(self, user_id_list, room_public_key, room_public_key_n, room_private_key):
        c = self.get_cursor()
        # user_nanme_list=[]
        user_name_list = ''
        flag = 0
        for user_id in user_id_list:
            print(user_id)
            print(type(user_id))
            user_name = c.execute("SELECT username from users where user_id=?", [user_id]).fetchone()[0]
            if flag == 0:
                user_name_list = user_name
            else:
                user_name_list = user_name_list + '-' + user_name
            flag = flag + 1
        self.insert_room_information(user_name_list, room_public_key, room_public_key_n, room_private_key)
        self.conn.commit()
        for user_id in user_id_list:
            room_id = c.execute("SELECT room_id from room where room_name=?", [user_name_list]).fetchall()[-1][0]
            print("member_room_id", room_id)
            self.insert_member(room_id, user_id)
        return [room_id, user_name_list]

    def query_all_other_users(self, username):  # 返回其他用户ID和NAME
        c = self.get_cursor()
        all_users = c.execute(
            "select user_id,username from users EXCEPT select user_id,username from users where username=?",
            [username]).fetchall()
        # print(all_users)
        all_user = dict(all_users)
        # print(all_user)
        return all_user

    def query_room_information(self, username):  # 返回用户对应的房间信息
        user_id = self.query_userID_byname(username)
        c = self.get_cursor()
        room_id_list = []
        room_ids = c.execute("SELECT room_id from member where user_id=?", [user_id]).fetchall()
        for room_id in room_ids:
            index = room_id[0]
            room_id_list.append(index)
        room_info_list = []
        for room_id in room_id_list:
            room_info = c.execute("SELECT * from room where room_id=?", [room_id]).fetchone()
            room_info_list.append(room_info)
        room_information = dict(zip(room_id_list, room_info_list))
        return room_information
        # print(room_id_list)
        # print(room_name_list)
        # return [room_id_list,room_name_list]

    def query_all_history_information(self, room_id):  # 返回房间ID对应的历史信息
        c = self.get_cursor()
        all_message = c.execute("SELECT * FROM history WHERE room_id=?", [room_id]).fetchall()
        # message=dict(all_message)
        print(all_message)
        return all_message

    def insert_history_information(self, user_id, room_id, data):
        c = self.get_cursor()
        time = c.execute("SELECT datetime('now','localtime');").fetchone()[0]
        c.execute("INSERT into history (user_id,room_id, time ,data) values (?,?,?,?)",
                  [user_id, room_id, time, data])
        self.conn.commit()
        message_id = c.execute("SELECT message_id from history where room_id = ? and time = ?",
                  [room_id, time]).fetchone()[0]
        return message_id

    def query_user_id_by_room_id(self, room_id):
        c = self.get_cursor()
        room_id_list = []
        roomids = c.execute("SELECT user_id FROM member WHERE room_id=?", [room_id]).fetchall()
        for roomid in roomids:
            room_id_list.append(roomid[0])
        return room_id_list

    def query_data_by_msg_id(self, msg_id):
        c = self.get_cursor()
        data = c.execute("SELECT data FROM history WHERE message_id=?", [msg_id]).fetchone()[0]
        return data


    def delete_room(self,room_id):
        c = self.get_cursor()
        c.execute("DELETE FROM room WHERE room_id=?",[room_id])
        self.conn.commit()
